﻿CREATE OR REPLACE FUNCTION loop_f() RETURNS void AS $$
BEGIN
	FOR i IN 1 .. 1000 LOOP
		--PERFORM * FROM Customers WHERE "CustomerID" < 'DDY' ORDER BY "ContactName";
		PERFORM * FROM 
		(SELECT * FROM Customers INNER JOIN Orders Using ("CustomerID")
			WHERE "CustomerID" = 'QUEEN' ORDER BY "ContactName") AS a
		INNER JOIN
		(SELECT * FROM Products INNER JOIN Order_Details Using ("ProductID")
			WHERE Order_Details."UnitPrice"*"Quantity" > 1000  AND "SupplierID" = 20 ORDER BY "OrderID") AS b
		USING ("OrderID");
	END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM 
(SELECT * FROM Customers INNER JOIN Orders Using ("CustomerID")
	WHERE "CustomerID" = 'QUEEN' ORDER BY "ContactName") AS a
INNER JOIN
(SELECT * FROM Products INNER JOIN Order_Details Using ("ProductID")
	WHERE Order_Details."UnitPrice"*"Quantity" > 1000  AND "SupplierID" = 20 ORDER BY "OrderID") AS b
USING ("OrderID");
SELECT loop_f();

DROP INDEX IF EXISTS products_sup_id_i;
CREATE INDEX products_sup_id_i ON Products ("SupplierID");
DROP INDEX IF EXISTS products_sup_id_i;
DROP INDEX IF EXISTS order_details_price_i;
CREATE INDEX order_details_price_i ON Order_Details (("UnitPrice"*"Quantity"));
DROP INDEX IF EXISTS order_details_price_i;

DROP INDEX IF EXISTS customers_id_i;
CREATE INDEX customers_id_i ON Customers ("CustomerID");
DROP INDEX IF EXISTS customers_id_i;
DROP INDEX IF EXISTS orders_cid_i;
CREATE INDEX orders_cid_i ON Orders ("CustomerID");
DROP INDEX IF EXISTS orders_cid_i;

DROP INDEX IF EXISTS invisible;